package control;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Random;

import java.util.Calendar;

import ocsf.server.ConnectionToClient;

import server.EchoServer;
import server.mysqlConnection;

public abstract class AbstractControl 
{
	protected static Integer pass=2000;
	/**
	 * This function handles all the messages coming from the client and initializes a procedure for each given message. 
	 * @param msg - message from client.
	 * @param clientin - ConnectionToClient of the client
	 * @return ArrayList which contains a result for the given message. The first cell identifies the message and the rest is the returned data.
	 */
	public synchronized static Object initialize(String[] msg,ConnectionToClient clientin)
	{
		Object q = null;
		AbstractControl rst = null;
	  	switch(msg[0])
	  	{
	  		case "Login": //User
	  			q=login((String[])msg,clientin);
	  			break;
	  		case "Search"://User
	  			rst=new SearchControl();
	  			break;
	  		case "CreateNewReaderCard"://librarian
	  			rst=new ReaderCardControl();
	  			break;
	  		case "BorowBook"://librarian
	  			rst=new BorrowControl();
	  			break;
	  		case "UpdateReturnDate"://librarian
	  			q=updateReturnDate((String[])msg);
	  			break;
	  		case "InventoryManagement"://librarian
	  			rst=new InventoryControl();
	  			break;
	  		case "ReturnABook"://librarian
	  			rst=new ReturnControl();
	  			break;
	  		case "WatchReaderCard"://librarian
	  			rst=new WatchReaderCardControl();
	  			break;
	  		case "ViewBookOrders"://librarian
	  			q=viewBookOrders((String[])msg);
	  			break;
	  		case "UpdateDetails"://Subscriber
	  			q=updateDetails((String[])msg);
	  			break;
	  		case "ExtendBorrow": //Subscriber
	  			rst=new ExtendBorrowControl();
	  			break;
	  		case "ViewMyReaderCard": //Subscriber
	  			q=viewMyReaderCard((String[])msg);
	  			break;
	  		case "OrderBook"://Subscriber
	  			q=orderBook((String[])msg);
	  			break;
	  		case "Freeze"://Manager
	  			q=freeze((String[])msg);
	  			break;
	  		case "Reports"://Manager
	  			rst=new ReportsControl();
	  			break;
	  		case "WatchIndex":
	  			rst=new SearchControl();
	  			break;
	  		case "Logoff":
	  			q=logoff(clientin);
	  			break;
	  		case "CancelOrder":
	  			q=cancelOrder((String[])msg,clientin);
	  			break;
	  		case "DeleteMessage":
	  			q=delMessage((String[])msg);
	  			break;
	  		case "DeleteTestUsers":
	  			q=deleteTestUserFromAllTablesInDB();
	  			break;
	  		case "WatchEmpAndSub":
	  			q=WatchEmployeesAndSubscribers();
	  			break;
	  		case "GetLibrarianMessages":
	  			q=getLibrarianMsgs();
	  			break;
	  	}
	  	if(rst!=null) //rst gets  instance of one of his sons and do his function
	  		q=rst.anlyze(msg);
		return q;//q have a type of Object but when operation finish the phase in the part of the server q gets arrayList which contains data that we send back to the client which analyze the data to output

	  	
	}

	/**
	 * This function takes all details of subscribers and librarian from DB 
	 * @return ArrayList that contains the details of the subscribers and librarians
	 */
	private static Object WatchEmployeesAndSubscribers() {
		ArrayList<Object> result=new ArrayList<Object>();
		result.add("EmployeeAndSubscriber");
		String [][] subscribers=mysqlConnection.select("SELECT id,firstname, lastname, phone, email FROM users WHERE access_level='1';",5);
		String [][] employees=mysqlConnection.select("SELECT id,firstname, lastname, phone, email FROM users WHERE access_level='2';",5);
		result.add(employees);
		result.add(subscribers);
		return result;
	}

	/**
	 * Retrieves all the messages that were sent to the librarian.
	 * @return ArrayList that contains all the messages to the librarian
	 */
	private static Object getLibrarianMsgs() {
		ArrayList<Object> result=new ArrayList<Object>();
		result.add("LibrarianMessages");
		String query=new String("SELECT date,log FROM logs WHERE sid='0' order by date DESC;");
		String [][] logs=mysqlConnection.select(query,2);
		result.add(logs);
		return result;
	}
	
	/**
	 * Takes care of deleting librarian messages.
	 * @param msgFromClient contains the messages to delete. Can also delete all messages.
	 * @return the updated message list after the deletion.
	 */
	private static Object delMessage(String[] msgFromClient) {
		ArrayList<Object> result=new ArrayList<Object>();
		result.add("DeleteMessage");
		if(msgFromClient[1].contentEquals("SINGLE"))
		{
			mysqlConnection.update("DELETE FROM logs WHERE sid='0' AND date='"+msgFromClient[2]+"';");
		}
		else if (msgFromClient[1].contentEquals("ALL"))
		{
			mysqlConnection.update("DELETE FROM logs WHERE sid='0';");
		}
		String query=new String("SELECT date,log FROM logs WHERE sid='0' order by date DESC;");
		String [][] logs=mysqlConnection.select(query,2);
		result.add(logs);
		return result;
	}

	/**
	 * Handles the cancel order procedure.
	 * @param msgFromClient - contains the order you wish to delete.
	 * @param clientin - ConnectionToClient of the client.
	 * @return the updated orders list after the deletion.
	 */
	private static Object cancelOrder(String[] msgFromClient, ConnectionToClient clientin) {
		ArrayList<Object> result=new ArrayList<Object>();
		EchoServer.bookOrdersQueue.get(Integer.parseInt(msgFromClient[2])).remove(Integer.parseInt(msgFromClient[1]));
		mysqlConnection.update("DELETE FROM orders WHERE sid="+msgFromClient[1]+" AND bid="+msgFromClient[2]+";");
		mysqlConnection.update("UPDATE orders SET indexinqueue=indexinqueue-1 WHERE bid="+msgFromClient[2]+" AND indexinqueue>'" + msgFromClient[3] + "+1';");
		String query=new String("SELECT sid,users.firstname,users.lastname,indexinqueue FROM orders , users WHERE orders.bid='" + msgFromClient[2] + "' and orders.sid=users.id order by indexinqueue;");
		String [][] orders=mysqlConnection.select(query,4);
		result.add("cancelOrder");
		result.add(orders);
		return result;
	}

	/**
	 * Handles the logoff procedure.
	 * @param client - ConnectionToClient of the client
	 * @return - "logoff" - logoff succeeded.
	 */
	private static Object logoff(ConnectionToClient client) {
		ArrayList<Object> s=new ArrayList<Object>();
		EchoServer.connectedclients.remove(client.getName());
		client.setName("User" + new Random().nextInt()%10000);
		s.add("logoff");
		return s;
	}

	/**Handles the freeze procedure.
	 * @param msgFromClient - contains the sid you want to freeze/unfreeze.
	 * @return ArrayList with result
	 */
	private static Object freeze(String[] msgFromClient) 
	{		
		ArrayList<Object> s=new ArrayList<Object>();
		s.add("freeze");

		Integer status=getStatus(msgFromClient[1]);
		s.add(status.toString());


		if(status==1)
		{
			String query=new String("UPDATE test.subscriber SET CardStatus=2 WHERE sId="+ msgFromClient[1]+" AND CardStatus=1;");
			System.out.println("Message received: " +query);
			s.add(mysqlConnection.update(query));
			logActivity(msgFromClient[1],"Reader card status changed to \"Freeze\".");
			return s;
		} else
			try {
				if(status==2  &&  !isGraduatedStudent(msgFromClient[1],getGraduationDate(msgFromClient[1])))
				{			
					String query=new String("UPDATE test.subscriber SET CardStatus=1 WHERE sId="+ msgFromClient[1]+" AND CardStatus=2;");
					System.out.println("Message received: " +query);
					s.add(mysqlConnection.update(query));
					logActivity(msgFromClient[1],"Reader card status changed to \"Active\".");
					return s;
				}
				else
				{
					s.add("FAIL");
					return s;
				}
			} catch (ParseException e) {
				s.add("FAIL");
				return s;
			}


	}

	/** 
	 * This function handles the order book procedure. 
	 * @param msgFromClient - contains the book the client wants to order
	 * @return "frozen" if user account is frozen, "borrowed" if user already have this book , 
	 * 		   "orderlist" if user already ordered the book and it's waiting for him to pick it up , 
	 * 		   "exists", if user already ordered the book,"full" if book order queue is full , "true" if order succeeded. 
	 */
	private static Object orderBook(String[] msgFromClient) {
		ArrayList<Object> s=new ArrayList<Object>();
		s.add("OrderBook");
		if((mysqlConnection.select("select cardstatus from subscriber where sid=" +msgFromClient[1],1)[0][0].toString()).contentEquals("2"))
		{
			s.add("frozen");
			return s;
		}
		String query2="Select sid from borrow where bid=" + msgFromClient[2] +" AND sid=" +msgFromClient[1] + " AND returndate='00/00/00'";
		if(mysqlConnection.select(query2,1).length!=0)
		{
			s.add("borrowed");
			return s;
		}
		query2="Select sid from saveorderlist where bid=" + msgFromClient[2] +" AND sid=" +msgFromClient[1];
		if(mysqlConnection.select(query2,1).length!=0)
		{
			s.add("orderlist");
			return s;
		}
		String query="Select bid,sid from orders where bid=" + msgFromClient[2] +" AND sid=" +msgFromClient[1];
		String temp[][]=mysqlConnection.select(query,2);
		if(temp.length==0)
		{
			if(EchoServer.bookOrdersQueue.get(Integer.parseInt(msgFromClient[2])).offer(Integer.parseInt(msgFromClient[1])))
			{
				s.add("true");
				Integer x = EchoServer.bookOrdersQueue.get(Integer.parseInt(msgFromClient[2])).size();
				mysqlConnection.update("insert into orders set bid=" + msgFromClient[2] + ",sid=" + msgFromClient[1]
						+ ",indexinqueue=" + x + ";" );
				logActivity(msgFromClient[1],"Ordered Book: " + getNameBook(msgFromClient[2]) + " ,bid: " +msgFromClient[2]);
			}
			else
			{
				s.add("full");
			}
		}
		else
		{
			s.add("exists");
		}
		return s;
	}

	
	
	
	/**
	 * This function takes subscriber information from DB
	 * @param msgFromClient - contains the subscriber id 
	 * @return subscriber information 
	 */
	private static Object viewMyReaderCard(String[] msgFromClient) {
		ArrayList<Object> result =new ArrayList<Object>();
			result.add("ViewMyReaderCard");
			String query=new String("SELECT name, borrowDate, borrow.expectedReturnDate, returnDate, incident  FROM borrow, books WHERE books.bid=borrow.bId AND books.copy=borrow.copy AND sId="+msgFromClient[1]+" AND returndate<>'00/00/00'"+";");
			String[][] history=mysqlConnection.select(query,5);
			result.add(history);		
			String[][] borrows=null;
			String query1=new String("SELECT name, borrowDate, borrow.expectedReturnDate FROM borrow, books WHERE books.bid=borrow.bId AND books.copy=borrow.copy AND returnDate='00/00/00' AND sId="+msgFromClient[1]+";");
			borrows=mysqlConnection.select(query1,3);
			result.add(borrows);	
			query1=new String("SELECT ID, FirstName, LastName, Phone, email, CardStatus" +
					" FROM users, subscriber WHERE users.ID=subscriber.sId AND sid=" +msgFromClient[1] +";");
			String[][] details=mysqlConnection.select(query1, 6);
			result.add(details);
			query1=new String("SELECT date,log FROM logs WHERE sid=" +msgFromClient[1] +" order by date DESC;");
			String[][] actions=mysqlConnection.select(query1, 2);
			result.add(actions);
			return result;	
		}


	
	
	/** This function handles the update details procedure. 
	 * @param msgFromClient - contains the new users details to update.
	 * @return ArrayList with result
	 */
	private static Object updateDetails(String[] msgFromClient) 
	{
		ArrayList<Object> s=new ArrayList<Object>();
		
		
		if(msgFromClient[1].equals("update"))
		{
			String[][] details;
			s.add("BringDetails");
			String query=new String("SELECT u.Phone, u.email, u.FirstName, u.LastName FROM test.users u, test.subscriber s WHERE u.ID="+msgFromClient[2]+" AND s.sId="+msgFromClient[2]+";");
			details=mysqlConnection.select(query,4);
			s.add(details);
			return s;	
		}
		else if(msgFromClient[1].equals("ChangePassword"))
		{
			s.add("UpdateDetails");
			String[][] details;
			String query=new String("SELECT u.password, u.email FROM test.users u, test.subscriber s WHERE u.ID="+msgFromClient[4]+" AND s.sId="+msgFromClient[4]+";");
			details=mysqlConnection.select(query,2);
			if(msgFromClient[2].equals(details[0][0]))
			{
				String query2=new String("UPDATE test.users SET password=\""+msgFromClient[3]+"\" WHERE ID="+msgFromClient[4] +";");
				s.add(mysqlConnection.update(query2));		
				logActivity(msgFromClient[4],"Updated Password");
			}
			else
				s.add("ERROR: Old password does not mach current password!");
			return s;
		}
		else 
		{
			s.add("UpdateDetails");
			String query=new String("UPDATE test.users SET FirstName=\""+msgFromClient[4]+"\", LastName=\""+msgFromClient[5]+"\", email=\""+msgFromClient[2]+"\", Phone=\""+ msgFromClient[1]+ "\" WHERE ID="+msgFromClient[3]+";");
			String a=mysqlConnection.update( query);
			s.add(a);
			logActivity(msgFromClient[3],"Updated details to: First Name -  "+msgFromClient[4]+", Last Name - " +msgFromClient[5]+", EMail  - "+msgFromClient[2]+", Phone - "+msgFromClient[1]);
			return s;
		}
		
		
	}
	/**
	 * Retrieves all the orders of a specific book.
	 * @param msgFromClient - contains the book id you wish to view its orders.
	 * @return a table containing all the orders of the specific book.
	 */
	private static Object viewBookOrders(String[] msgFromClient) {
		ArrayList<Object> result =new ArrayList<Object>();
		result.add("ViewBookOrders");
		String query=new String("SELECT sid,users.firstname,users.lastname,indexinqueue FROM orders , users WHERE orders.bid='" + msgFromClient[1] + "' and orders.sid=users.id order by indexinqueue;");
		String [][] orders=mysqlConnection.select(query,4);
		result.add(orders);
		result.add(msgFromClient[1]);
		return result;
		
	}

	/**
	 * In this function we execute the operation up date return book
	 * @param msgFromClient
	 * @return ArrayList with result
	 */
	private static Object updateReturnDate(String[] msgFromClient)
	{
		ArrayList<Object> s=new ArrayList<Object>();
		String a = null;
		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
		s.add(msgFromClient[0]);
		if(EchoServer.bookOrdersQueue.get(Integer.parseInt(msgFromClient[2])).isEmpty())//Checking if this book have orders
		{
			Calendar returnDate = Calendar.getInstance();
			try 
			{
				returnDate.setTime(dateFormat.parse(msgFromClient[5]));
			} 
			catch (ParseException e) 
			{
				e.printStackTrace();
			}
			if (bookIsPopular(msgFromClient[2],msgFromClient[3])) //We check if this book is popular and by that we decide what would be the expected return date
			{
				returnDate.add(Calendar.DATE, 3);
			} else
				returnDate.add(Calendar.DATE, 14);
			String returnD =dateFormat.format(returnDate.getTime());
			
			String query=new String("UPDATE borrow SET expectedReturnDate="+"'"+returnD+"'"+" where sid="+msgFromClient[1]+" and bid="+msgFromClient[2]+" and copy="+msgFromClient[3]+" and expectedReturnDate="+"'"+msgFromClient[5]+"'");
			a=mysqlConnection.update(query);
			if(!a.contains("FAIL"))
			{
				String key=new String("UPDATE books SET expectedReturnDate="+"'"+returnD+"'"+" where bid="+msgFromClient[2]+" and copy="+msgFromClient[3]);
				a=mysqlConnection.update(key);
			}
			logActivity(msgFromClient[1],msgFromClient[0]+" "+getNameBook(msgFromClient[2])+" "+msgFromClient[2]+"-"+msgFromClient[3]+" "+msgFromClient[4]+" "+msgFromClient[5]);
		}
		else	
			a=new String("This book is ordered!");
		s.add(a);
		return s;
	}

	/**
	 * Every child  of this abstract class analyze in a different way the message that came from the client
	 * @param msg
	 * @return ArrayList with result
	 */
	public abstract Object anlyze(String[] msg);


	/**
	 * Every child of this abstract class check exist of data in DB
	 * @return ArrayList with result
	 */
	public abstract Boolean checkExist();
	
	

	/**
	 * Takes care of the login procedure.
	 * @param msgFromClient - contains the client username and password
	 * @param client - ConnectionToClient of the client
	 * @return "already" if client already connected, "Success" if login succeeded , "FAIL" if login falied.
	 */
	private static Object login(String[] msgFromClient,ConnectionToClient client) 
	{
		ArrayList<Object> s=new ArrayList<Object>();
		s.add("Login");
		String username=msgFromClient[1];
		String password=msgFromClient[2];
		String query=new String("SELECT * FROM test.users WHERE id="+username+";");
		String[][] rst=mysqlConnection.select(query, 7);
		if(rst.length!=0)
		{
			if(rst[0][6].contentEquals(password))
			{
				if(rst[0][5].contentEquals("1"))
				{
					if(mysqlConnection.select("SELECT cardstatus FROM test.subscriber WHERE sid="+rst[0][0]+";",1)[0][0].contentEquals("0"))
					{	
						s.add("locked");
						return s;
					}
				}
				if(EchoServer.connectedclients.contains(rst[0][0] + " " + rst[0][1] + " " + rst[0][2]))
				{
					s.add("already");
				}
				else
				{
					EchoServer.connectedclients.add(rst[0][0] + " " + rst[0][1] + " " + rst[0][2]);
					client.setName(rst[0][0] + " " + rst[0][1] + " " + rst[0][2]);
					s.add("Success");
					s.add(rst);
					logActivity(rst[0][0],"Logged in successfully from " + client.getInetAddress());	
				}
			}
			else
				s.add("FAIL");
		}
		else
			s.add("FAIL");
		return s;	
	}
	
	
	/**
	 * Get reader card status
	 * @param sid
	 * @return 0 if not locked, 1 if active , 2 if frozen.
	 */
	private static int getStatus(String sid)
	{
		String query=new String("SELECT CardStatus FROM test.subscriber WHERE sId="+sid+";");
		String[][] rst=mysqlConnection.select(query, 1);
		if(rst.length==0 ) 
			return -1;
		
		return Integer.parseInt(rst[0][0]);
	}
	
	/**
	 * Get name book
	 * @param bid
	 * @return String - book name
	 */
	protected static String getNameBook(String bid)
	{
		String key=new String("Select name from books WHERE bid="+bid);
		String[][] rst=mysqlConnection.select(key,1);
		if(rst.length!=0) 
			return rst[0][0];
		return null;
	}
	
	/**
	 * Checking if specific book is popular
	 * @param bid - book id
	 * @param copy - book copy
	 * @return true if popular, false if not.
	 */
	protected static boolean bookIsPopular(String bid,String copy) 
	{
		String key=new String("Select ispopular from books WHERE bid="+bid+" AND copy="+copy);
		String[][] rst=mysqlConnection.select(key,1);
		if(rst.length!=0 && rst[0][0].compareTo("1")==0) 
			return true;
		return false;
	}
	
 /**
 * Used to insert data to the log database, Every action performed on a user is logged.
 * @param sid - user sid to save the log to. 
 * @param log - log message.
 * @return Update succeeded/failed
 */
static String logActivity(String sid,String log)
	{
		String queryinsert=new String("INSERT into test.logs SET sId='"+sid+"',date=now(),log='" + log + "' ,rand='" + new Random().nextInt()%100 + "';");
		String update=mysqlConnection.update(queryinsert);
		return update;
	}
	
	
	/**used to get the graduation date of a student
	 * @param sid - user sid to save the log to.
	 * @return graduation date.
	 */
	public static String getGraduationDate(String sid)
	{
		String key=new String("Select GraduateDate from test.subscriber WHERE sId="+sid+";");
		String[][] rst=mysqlConnection.select(key,1);		
		return rst[0][0];		
	}
	
	/**
	 * Check if student graduated
	 * @param sid
	 * @param GraduateDate
	 * @return true/false
	 * @throws ParseException
	 */
	protected static boolean isGraduatedStudent(String sid,String GraduateDate) throws ParseException 
	{
		Calendar cal = Calendar.getInstance();
		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
		String now = dateFormat.format(cal.getTime());
		
		
    	Date date1 = dateFormat.parse(now);
    	Date date2 = dateFormat.parse(GraduateDate);

    	
    	if(date1.compareTo(date2)>0 || date1.compareTo(date2)==0)//now is today or after GraduateDate
    	{
    		String[][] ReturnDate=mysqlConnection.select("Select returnDate from borrow where sid="+sid+" and returnDate='00/00/00' ",1);
    		if(ReturnDate.length>0 && ReturnDate[0][0].compareTo("00/00/00")==0)//Still have books and need to return them
    		{
    			mysqlConnection.update("update subscriber set CardStatus='2' where sid="+sid);
    		}
    		else
    		{
    			mysqlConnection.update("update subscriber set CardStatus='0' where sid="+sid);
    		}
    		return true;
    	}
		return false;
	}
	
	
	
	/**
	 * When we need to delete the things that we create while testing the project
	 * @return ArrayList with result
	 */
	public static Object deleteTestUserFromAllTablesInDB()
	{
		ArrayList<Object> s=new ArrayList<Object>();
		mysqlConnection.update("DELETE FROM users WHERE id='-1' ;");
		mysqlConnection.update("DELETE FROM borrow WHERE sid='-1';");
		mysqlConnection.update("DELETE FROM subscriber WHERE sid='-1';");
		mysqlConnection.update("DELETE FROM orders WHERE sid='-1';");
		mysqlConnection.update("DELETE FROM saveorderlist WHERE sid='-1';");
		mysqlConnection.update("DELETE FROM logs WHERE sid='-1';");
		mysqlConnection.update("UPDATE books SET isborrowed='0' , expectedreturndate=NULL where bid='1' AND copy='1' ;");
		mysqlConnection.update("UPDATE books SET isborrowed='0' , expectedreturndate=NULL where bid='8' AND copy='1' ;");
		mysqlConnection.update("DELETE FROM borrow WHERE bid='9';");
		mysqlConnection.update("UPDATE books SET isborrowed='0' , expectedreturndate=NULL where bid='9' AND copy='1' ;");
		
		s.add("deleteTest");
		return s;
		
	}
	
	/** gets user's email
	 * @param sid - user sid to save the log to.
	 * @return email of the subscriber with this sid
	 */
	public String getEmail(String sid)
	{
		String key=new String("Select email from test.users WHERE ID="+sid+";");
		String[][] rst=mysqlConnection.select(key,1);		
		return rst[0][0];		
	}
	
	
	
}
